package com.gem.util;

import java.io.*;
import java.sql.*;
import java.util.Scanner;

/**
 * 写一个工具类,用来模拟cmd mysql命令行功能
 * 1.执行任意sql语句
 * 2.执行任意sql脚本
 */
public class SqlCmd {
	public static void main(String[] args) {
		Scanner scanner = new Scanner(System.in);
		System.out.print("mysql> ");
		String line = scanner.nextLine();
		SqlCmd.execute(line);
		scanner.close();
	}

	/**
	 * 执行SQL的方法
	 */
	public static void execute(String line) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = Dbcp.getInstance().getConnection();
			conn.setAutoCommit(false);
			if (line.contains(":")) {
				//说明输入的脚本路径
				File file = new File(line);
				if (file.exists() && file.isFile() && file.getName().endsWith("sql")) {
					BufferedReader br = new BufferedReader(new FileReader(file));
					String eachLine = null;
					StringBuilder sql = new StringBuilder();
					while ((eachLine = br.readLine()) != null) {
						//判断是否是注释
						if (eachLine.startsWith("--")) {
							continue;
						}
						sql.append(eachLine);
						//判断当前sql是否是一句完整的SQL语句
						if (sql.toString().endsWith(";")) {
							//回调递归
							execute(sql.toString());
							//清空builder
							sql.delete(0, sql.capacity());
						}
					}
					br.close();
				} else {
					System.out.println("参数有误...");
				}
			} else {
				//说明输入的是sql语句
				pst = conn.prepareStatement(line);
				//execute()方法返回true表示是查询,返回false表示是增删改
				//注意:调用execute()方法后语句对象就已经执行过了!!!
				if (pst.execute()) {
					//获取结果集
					rs = pst.getResultSet();
					//获取结果集元数据
					ResultSetMetaData rsmd = rs.getMetaData();
					//获取列数
					int columnCount = rsmd.getColumnCount();
					//处理结果集
					for (int i = 1; i <= columnCount; i++) {
						System.out.printf("%-15s\t", rsmd.getColumnName(i));
					}
					System.out.println();
					while (rs.next()) {
						for (int i = 1; i <= columnCount; i++) {
							System.out.printf("%-15s\t", rs.getString(i));
						}
						System.out.println();
					}
				} else {
					//说明是增删改操作
					int count = pst.getUpdateCount();//获取影响行数
					System.out.println("一共影响了[" + count + "]行记录.");

				}
			}
			conn.commit();
		} catch (Exception e) {
			if (conn != null) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			e.printStackTrace();
		} finally {
			Dbcp.getInstance().close(conn, pst, rs);
		}
	}
}
